package com.it2297.model;

import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.it2297.main.App;

public class Comment {
	private int commentId;
	private String title;
	private String message;
	private Category category;
	private Timestamp dateCreate;
	private Timestamp dateUpdate;
	private String userId;

	public enum Category {
		PRODUCT, FUNDRAISE, WORKSHOP, DONATION, OLD_ITEM;
	}

	public int getCommentId() {
		return commentId;
	}

	public void setComment(int comment) {
		this.commentId = comment;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public Category getCategory() {
		return category;
	}

	public void setCategory(Category category) {
		this.category = category;
	}

	public Timestamp getDateCreate() {
		return dateCreate;
	}

	public void setDateCreate(Timestamp dateCreate) {
		this.dateCreate = dateCreate;
	}

	public Timestamp getDateUpdate() {
		return dateUpdate;
	}

	public void setDateUpdate(Timestamp dateUpdate) {
		this.dateUpdate = dateUpdate;
	}

	public String getUserId() {
		return userId;
	}

	public void setUserId(String userid) {
		this.userId = userid;
	}

	public static ArrayList<Comment> getMultipleComment(int index, int count, String productName) {
		ArrayList<Comment> multi = new ArrayList<Comment>();

		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.comment where title = ? ORDER BY date_create DESC LIMIT ?,?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setString(1, productName);
			ps.setInt(2, index);
			ps.setInt(3, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Comment comment = new Comment();
				comment.setAttributes(rs);
				multi.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}
	
	public void CreateComment(Comment comment) {

		try {
			App.mySqlConnection.open();
			String sql = "INSERT INTO americano.comment(nric, title, body ,date_create, date_update)";
			sql += "VALUES(?,?,?,?,?)";

			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);

			ps.setString(1, comment.getUserId());
			ps.setString(2, comment.getTitle());
			ps.setString(3, comment.getMessage());
			ps.setTimestamp(4, comment.getDateCreate());
			ps.setTimestamp(5, comment.getDateUpdate());

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
	}
	
	public boolean delComment(Comment comment){
		boolean valid = false;
		
		try {
			App.mySqlConnection.open();
			String sql = "DELETE FROM americano.comment WHERE nric=? AND title =?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setString(1, comment.getUserId());
			ps.setString(2, comment.getTitle());
			ps.executeUpdate();
			valid =true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		
		return valid;
	}

	private void setAttributes(ResultSet rs) {
		try {
			this.setTitle(rs.getString("title"));
			this.setUserId(rs.getString("nric"));
			this.setMessage(rs.getString("body"));
			this.setDateCreate(rs.getTimestamp("date_create"));
			this.setDateUpdate(rs.getTimestamp("date_update"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static int getRecordNum() {
		int recordNum = 0;
		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.comment";
			PreparedStatement preparedStatement = App.mySqlConnection
					.getConnection().prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return recordNum;
	}

}
